﻿using System;
using System.Collections.Generic;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Data;
using System.IO;
using System.Diagnostics;
namespace EasyExcel
{
    // <summary>
    /// 导出到Excel的操作类 作者：蔡水明 2009-03-02
    /// </summary>
    public class ExportToExcel
    {
        private Microsoft.Office.Interop.Excel.Application excelApplication;
        /// <summary>
        /// Excel程序
        /// </summary>
        public Microsoft.Office.Interop.Excel.Application ExcelApplication
        {
            get { return excelApplication; }
            set
            {
                excelApplication = value;

            }
        }
        /// <summary>
        /// 打开工作簿
        /// </summary>
        private void AddWorkBooks()
        {
            if (excelApplication != null)
            {
                excelApplication.Visible = Visible;
                if (File.Exists(MbFileName.ToString()))
                {
                    if (string.IsNullOrEmpty(password))
                        //WorkBook = ExcelApplication.Workbooks.Add(MbFileName);
                        WorkBook = ExcelApplication.Workbooks.Open(MbFileName.ToString(), Missing.Value, false, Missing.Value, Missing.Value, Missing.Value,
                           Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                    else
                    {
                        WorkBook = ExcelApplication.Workbooks.Open(MbFileName.ToString(), Missing.Value, false, Missing.Value, "123456", Missing.Value,
                           Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                    }
                }
                else
                    WorkBook = ExcelApplication.Workbooks.Add(Missing.Value);

            }

        }
        private Excel.Workbook workBook;
        /// <summary>
        /// Excel工作簿
        /// </summary>
        public Excel.Workbook WorkBook
        {
            get { return workBook; }
            set
            {
                workBook = value;
                if (workBook != null)
                    WorkSheet = (Excel.Worksheet)workBook.ActiveSheet;
            }
        }
        private Excel.Worksheet workSheet;
        /// <summary>
        /// Excel工作表
        /// </summary>
        public Excel.Worksheet WorkSheet
        {
            get { return workSheet; }
            set { workSheet = value; }
        }
        private object mbFileName = Missing.Value;
        private string password = string.Empty;
        /// <summary>
        /// 模板文件(相对路径）
        /// </summary>
        public object MbFileName
        {
            get { return mbFileName; }
            set
            {
                mbFileName = value;
                string tmpStr = (string)mbFileName;
                //如果是相对路径
                if (!tmpStr.Contains(":"))
                    tmpStr = Environment.CurrentDirectory + "//" + tmpStr;
                mbFileName = (object)tmpStr;
            }
        }
        private bool visible = true;
        /// <summary>
        /// Excel窗口的可见性
        /// </summary>
        public bool Visible
        {
            get { return visible; }
            set { visible = value; }
        }
        private string foolter = "第 &P 页，共 &N 页  设计人：蔡水明";
        /// <summary>
        /// 页脚文字
        /// </summary>
        public string Foolter
        {
            get { return foolter; }
            set
            {
                foolter = value;
                WorkSheet.PageSetup.CenterFooter = foolter;
            }
        }
        public ExportToExcel()
        {
            ExcelApplication = new Microsoft.Office.Interop.Excel.Application();
            AddWorkBooks();
        }
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="mb">模板文件</param>
        /// <param name="vis">Excel的可见性</param>
        public ExportToExcel(object mb, bool vis = false)
        {
            MbFileName = mb;
            Visible = vis;
            ExcelApplication = new Microsoft.Office.Interop.Excel.Application();
            AddWorkBooks();
        }
        /// <summary>
        /// 带密码构造函数
        /// </summary>
        /// <param name="mb">模板文件</param>
        /// <param name="vis">Excel的可见性</param>
        public ExportToExcel(object mb, string pass, bool vis)
        {
            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();
            MbFileName = mb;
            Visible = vis;
            password = pass;
            ExcelApplication = new Microsoft.Office.Interop.Excel.Application();
            AddWorkBooks();
        }
        ~ExportToExcel()
        {
            workSheet = null;
            workBook = null;
            ExcelApplication = null;
        }
        /// <summary>
        /// 将一个表中所有数据导出
        /// </summary>
        /// <param name="dt">数据表</param>
        /// <param name="startRow">开始行</param>
        /// <param name="startCol">开始列</param>
        public void DataTableExp(DataTable dt, int startRow, int startCol)
        {
            int CurRow, CurCol;
            CurRow = startRow; CurCol = startCol;
            foreach (DataRow dr in dt.Rows)
            {
                foreach (DataColumn dc in dt.Columns)
                {
                    WorkSheet.Cells[CurRow, CurCol] = dr[dc];
                    CurCol++;
                }
                CurRow++;
                CurCol = startCol;
            }
        }
        /// <summary>
        /// 设置内外都有边框
        /// </summary>
        public void SetAllBorders(int StartRow, int StartCol, int EndRow, int EndCol)
        {
            Excel.Range R = ExcelApplication.Range[WorkSheet.Cells[StartRow, StartCol], WorkSheet.Cells[EndRow, EndCol]];
            R.Borders.LineStyle = 1;

            //Excel.Range R = WorkSheet.get_Range(
            //    WorkSheet.Cells[StartRow, StartCol], WorkSheet.Cells[EndRow, EndCol]);

            //R.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
            //R.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
            //R.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
            //R.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
            //R.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
            //R.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
        }
        /// <summary>
        /// 设置内外都有边框
        /// </summary>
        public void SetAllBorders(int StartRow, int StartCol, int EndRow, int EndCol, string SheetName)
        {
            WorkSheet = WorkBook.Sheets[SheetName] as Excel.Worksheet;
            Excel.Range R = ExcelApplication.Range[WorkSheet.Cells[StartRow, StartCol], WorkSheet.Cells[EndRow, EndCol]];
            R.Borders.LineStyle = 1;

            //Excel.Range R = WorkSheet.get_Range(
            //    WorkSheet.Cells[StartRow, StartCol], WorkSheet.Cells[EndRow, EndCol]);

            //R.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
            //R.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
            //R.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
            //R.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
            //R.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
            //R.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
        }
        /// <summary>
        /// 打印预览
        /// </summary>
        public void PrintPreView()
        {
            ExcelApplication.Visible = true;
            WorkSheet.PrintPreview(Missing.Value);
            ExcelApplication.Visible = false;
        }
        /// <summary>
        /// 直接打印
        /// </summary>
        public void Print()
        {
            ExcelApplication.Visible = false;
            WorkSheet.PrintOutEx();            
        }
        /// <summary>
        /// 设置单元格的值
        /// </summary>
        /// <param name="Row"></param>
        /// <param name="col"></param>
        /// <param name="value"></param>
        public void SetCellValue(int Row, int Col, object value)
        {
            WorkSheet.Cells[Row, Col] = value;
        }

        /// <summary>
        /// 设置单元格的值
        /// </summary>
        /// <param name="Row"></param>
        /// <param name="col"></param>
        /// <param name="value"></param>
        public void SetCellValue(int Row, int Col, object value, int SheetIndex)
        {
            WorkSheet = WorkBook.Sheets[SheetIndex] as Excel.Worksheet;
            workSheet.Unprotect("myworksheet");
            WorkSheet.Cells[Row, Col] = value;
        }
        public int SheetName2Index(string sName)
        {
            var ws = WorkBook.Sheets[sName] as Excel.Worksheet;
            return ws.Index;
        }
        /// <summary>
        /// 设置单元格的值
        /// </summary>
        /// <param name="Row"></param>
        /// <param name="col"></param>
        /// <param name="value"></param>
        public void SetCellValue(int Row, int Col, object value, string SheetName)
        {
            WorkSheet = WorkBook.Sheets[SheetName] as Excel.Worksheet;
            WorkSheet.Cells[Row, Col] = value;
        }
        /// <summary>
        /// 复制工作表
        /// </summary>
        public void SheetCopy(string sourceName, string NewName)
        {
            if (sourceName == NewName) { WorkBook.ActiveSheet.Name = sourceName; return; }
            WorkSheet = WorkBook.Sheets[sourceName] as Excel.Worksheet;
            //复制的表在前面
            WorkSheet.Copy(Type.Missing, WorkBook.Sheets[sourceName]);
            //if (workBook.Sheets[NewName] > 1)
            //    NewName += "I";
            WorkBook.ActiveSheet.Name = NewName;
            ////复制的表在后面
            //WorkSheet.Copy(WorkBook.Sheets[sourceName], Type.Missing);
            //WorkBook.ActiveSheet.Name = NewName;
        }
        /// <summary>
        /// 移动工作表到指定工作表后面
        /// </summary>
        /// <param name="SourceName">要移动的</param>
        /// <param name="AfterName">目标</param>
        public void MoveSheetAfter(string SourceName, string AfterName)
        {
            var ws = WorkBook.Sheets[SourceName] as Excel.Worksheet;//要移动的工作表
            ws.Move(Type.Missing, WorkBook.Sheets[AfterName]);

        }
        /// <summary>
        /// 移动工作表到指定工作表前面
        /// </summary>
        /// <param name="SourceName">要移动的</param>
        /// <param name="BeforeName">目标</param>
        public void MoveSheetBefore(String SourceName, string BeforeName)
        {
            var ws = WorkBook.Sheets[SourceName] as Excel.Worksheet;//要移动的工作表
            ws.Move(WorkBook.Sheets[BeforeName], Type.Missing);
        }
        /// <summary>
        /// 删除指定工作表
        /// </summary>
        /// <param name="sourceName"></param>
        public void DelSheet(string sourceName)
        {
            if (WorkBook.Sheets.Count == 1) return;
            excelApplication.DisplayAlerts = false;
            WorkSheet = WorkBook.Sheets[sourceName] as Excel.Worksheet;
            WorkSheet.Delete();
            WorkSheet = WorkBook.Sheets[1] as Excel.Worksheet;

        }
        /// <summary>
        /// 删除不在清单中的工作表
        /// </summary>
        /// <param name="sourceName"></param>
        public void DelSheetList(List<string> Lst)
        {
            if (Lst.Count == 0) return;
            excelApplication.DisplayAlerts = false;
            for (int i = WorkBook.Sheets.Count; i > 0; i--)
            {
                WorkSheet = WorkBook.Sheets[i] as Excel.Worksheet;
                if (!Lst.Contains(WorkSheet.Name))
                {
                    WorkSheet.Delete();
                }

            }
            WorkSheet = WorkBook.Sheets[1] as Excel.Worksheet;

        }
        public void SheetRename(string theOldName, string theNewName)
        {

            WorkBook.Sheets[theOldName].Name = theNewName;

        }
        public bool Convert2PDF(string targetPath)
        {
            bool result = false;
            try
            {
                object missing = Type.Missing;
                object target = targetPath;
                WorkBook.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, target, Excel.XlFixedFormatQuality.xlQualityStandard, true, false, missing, missing, missing, missing);
                result = true;
            }
            catch
            {
                result = false;
            }
            return result;
        }
        public bool Convert2PDF(string sourcePath, string targetPath)
        {
            bool result;
            object missing = Type.Missing;
            Excel.Application application = null;
            Excel.Workbook workBook = null;
            try
            {
                application = new Excel.Application();
                object target = targetPath;
                workBook = application.Workbooks.Open(sourcePath, missing, missing, missing, missing, missing,
                       missing, missing, missing, missing, missing, missing, missing, missing, missing);
                workBook.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, target, Excel.XlFixedFormatQuality.xlQualityStandard, true, false, missing, missing, missing, missing);
                result = true;
            }
            catch
            {
                result = false;
            }
            finally
            {
                if (workBook != null)
                {
                    workBook.Close(true, missing, missing);
                    workBook = null;
                }
                if (application != null)
                {
                    application.Quit();
                    application = null;
                }
                GC.Collect();
                GC.WaitForPendingFinalizers();
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
            return result;
        }
        /// <summary>
        /// 读取单元格的值
        /// </summary>
        /// <param name="Row"></param>
        /// <param name="Col"></param>
        /// <returns></returns>
        public object GetCellValue(int Row, int Col)
        {
            Excel.Range r = (Excel.Range)WorkSheet.Cells[Row, Col];
            return r.Value2;
        }
        /// <summary>
        /// 退出
        /// </summary>
        public void Close()
        {
            excelApplication.DisplayAlerts = false;
            excelApplication.AlertBeforeOverwriting = false;
            excelApplication.Quit();
            excelApplication = null;
        }
        /// <summary>
        /// 保存
        /// </summary>
        /// <param name="strFile"></param>
        public void Save(string strFile)
        {
            excelApplication.DisplayAlerts = false;
            excelApplication.AlertBeforeOverwriting = false;
            workSheet.SaveAs(@strFile, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            //    WorkBook.SaveAs(@strFile,Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
            //Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value,
            //Missing.Value, Missing.Value); 

        }
        /// <summary>
        /// 彻底退出，解决退出后系统进程里面还有Excel的问题
        /// </summary>
        public void CloseAll()
        {

            if (WorkBook != null)
            {
                WorkBook.Close(false, Type.Missing, Type.Missing);
                WorkSheet = null;
                WorkBook = null;
            }
            if (ExcelApplication != null)
            {
                excelApplication.DisplayAlerts = false;
                excelApplication.AlertBeforeOverwriting = false;
                ExcelApplication.Quit();
                ExcelApplication = null;
            }
            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();
        }
        /// 杀掉进程
        public void KillExcel()
        {            
            //System.Diagnostics.Process myproc = new System.Diagnostics.Process();
            //得到所有打开的进程
            try
            {
                foreach (Process thisproc in Process.GetProcessesByName("Excel"))
                {
                    if (!thisproc.CloseMainWindow())
                    {
                        thisproc.Kill();
                    }
                }
            }
            catch
            {

            }

        }

        /// <summary>
        /// 插入图片
        /// </summary>
        /// <param name="sheet">工作表</param>
        /// <param name="imageFilePath">图片的绝对路径</param>
        /// <param name="rowNumber">单元格行号</param>
        /// <param name="columnNumber">单元格列号</param>
        /// <returns></returns>     
        public void AddImage(string imageFilePath, int rowNumber, int columnNumber, string SheetName)
        {
            if (File.Exists(imageFilePath) == false) return;
            WorkSheet = WorkBook.Sheets[SheetName] as Excel.Worksheet;
            WorkSheet.Activate();
            Excel.Range range = (Excel.Range)WorkSheet.Cells[rowNumber, columnNumber];
            range.Select();
            Excel.Pictures pics = WorkSheet.Pictures(System.Reflection.Missing.Value);
            Excel.Picture pic = pics.Insert(imageFilePath, System.Reflection.Missing.Value);
            pic.Left = (double)range.Left;
            pic.Top = (double)range.Top;


        }
        /// <summary>
        /// 插入图片
        /// </summary>
        /// <param name="sheet">工作表</param>
        /// <param name="imageFilePath">图片的绝对路径</param>
        /// <param name="rowNumber">单元格行号</param>
        /// <param name="columnNumber">单元格列号</param>
        /// <returns></returns>
        public void AddImage(string imageFilePath, int rowNumber, int columnNumber, int SheetName)
        {
            if (File.Exists(imageFilePath) == false) return;
            //Excel.Worksheet sheet = WorkBook.Sheets[SheetName] as Excel.Worksheet;
            WorkSheet = WorkBook.Sheets[SheetName] as Excel.Worksheet;
            WorkSheet.Activate();
            Excel.Range range = (Excel.Range)WorkSheet.Cells[rowNumber, columnNumber];
            range.Select();
            Excel.Pictures pics = WorkSheet.Pictures(System.Reflection.Missing.Value);
            Excel.Picture pic = pics.Insert(imageFilePath, System.Reflection.Missing.Value);
            pic.Left = (double)range.Left;
            pic.Top = (double)range.Top;

        }        
        /// <summary>
        /// 添加二维码
        /// </summary>
        /// <param name="resourceName"></param>
        /// <param name="rowNumber"></param>
        /// <param name="columnNumber"></param>
        /// <param name="SheetName"></param>
        public void AddEwm(string content, string title, int rowNumber, int columnNumber, string SheetName)
        {
            string filePath = Path.Combine(Environment.CurrentDirectory, title + ".TanEwm");
            int sheetId = SheetName2Index(SheetName);
            Utilities.EwmHelper.Generate(content, title, filePath);
            AddImage(filePath, rowNumber, columnNumber, sheetId);
        }
        public void DelImage()
        {
            Excel.Pictures pics = WorkSheet.Pictures(System.Reflection.Missing.Value);
            if (pics == null) return;
            pics.Delete();
        }

    }
}
